﻿'// ========================================================================= '
'// ------------------------------------------------------------------------- '
'//!				\file		XLW.vb
'			        \date		june 2010
'			        \author		TNick
'
'			        \brief		
'
'
'   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'   This file is considered universal knowledge. No restrictions
'   are imposed; use as you see fit.
'   Please read ReadMe.txt and Licence.txt in root folder
'   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'
'   This code wraps the excel com interface. This way, with two versions of 
'  this dll we can have unique calls in main application
'// ------------------------------------------------------------------------- '
'// ========================================================================= '
'//
'//
'//
'//
'//  IMPORTS     ------------------------------------------------------------ '

Imports System
Imports Microsoft.Win32

#If EMPTY_STUB = false
Imports Microsoft.Office.Interop
#End If

'//  IMPORTS     ============================================================ '
'//
'//
'//
'//
'//  DEFINITIONS    --------------------------------------------------------- '

'//  DEFINITIONS    ========================================================= '
'//
'//
'//
'//
'//  DATA    ---------------------------------------------------------------- '


'//  DATA    ================================================================ '
'//
'//
'//
'//
'//  CLASS    --------------------------------------------------------------- '

Public NotInheritable  Class XLW

    Public Enum ERR
        OK = 0  '// no error
        EXCEPTION
        EXCEL_START '// Application could not be started
        NOTAFILE    '// file was not found
        BADINIT    '// objects are not in expected state
    End Enum



    '// --------------------------------------------------------------------- '
    '// DATA

    '// is used to hold status informations
    Dim eErrSts As ERR = ERR.OK

    #If EMPTY_STUB = false
    '// has a pointer to our instance of excel
    Private Shared ExcelApp As Excel.Application = Nothing
    '// Tells how manny instances of this class are active
    Private Shared WCount As Integer = 0

    '// this is the one that we're wrapping here
    Private ExcelWorkbook As Excel.Workbook = Nothing
    '// ===================================================================== '
    #End If



    '// --------------------------------------------------------------------- '
    '// QUERRIES
    Public ReadOnly Property ErrSts As ERR
        Get
            Return eErrSts
        End Get
    End Property
    #If EMPTY_STUB=false
        Friend ReadOnly Property ExAp As Excel.Application
            Get
                Return ExcelApp
            End Get
        End Property
        Friend ReadOnly Property WB As Excel.Workbook
            Get
                Return ExcelWorkbook
            End Get
        End Property
		Private EventDel_BeforeBookClose As Excel.AppEvents_WorkbookBeforeCloseEventHandler
    #End If
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// an instance of this class needs to be created for each workbook
    '// may either create a new worksheet or open an existing one
    Public Sub New(ByRef Optional sPathName As String = "")
    #If EMPTY_STUB
		Debug.Print("Empty stub")
        eErrSts = ERR.EXCEL_START
        Return      
    #Else
	Debug.Print("Not an empty stub")
    Try
		 Debug.Print("Enter try")
       '// see if we have Excel started; if we don't, create a new instance
        If ExcelApp Is Nothing Then
			Debug.Print("Excel not started")
            Try
                ExcelApp = DirectCast( _
                    System.Runtime.InteropServices.Marshal. _
                        GetActiveObject("Excel.Application"), Excel.Application)
            Catch ex As System.Exception
     		    Dim trace As System.Diagnostics.StackTrace = _
                    New System.Diagnostics.StackTrace(ex, True)
    		    Dim exceptionLineNumber As Integer =  _
                    trace.GetFrame(0).GetFileLineNumber()
               Dim sOut As String = vbNewLine & _
                "---------------------------------------" & vbNewLine & _
                "Exceptie: " & trace.GetFrame(0).GetMethod.Name & vbCrLf & _
    		     CStr(ex.Message) & "; " & vbCrLf & _
    		     trace.GetFrame(0).ToString & vbCrLf & _
                "---------------------------------------" & vbNewLine
				Debug.Print(sOut)
            End Try
        End If
        If Not (ExcelApp is Nothing) Then
			Debug.Print("Excel running")
            If (ExcelApp.Application.Visible = True) Then 
				Debug.Print("but visible")
                ReleaseComObject(ExcelApp)
                ExcelApp = Nothing
                ExcelApp = _
                    New Microsoft.Office.Interop.Excel.Application
            End If
        Else
			Debug.Print("Excel not running")
            ExcelApp = _
                New Microsoft.Office.Interop.Excel.Application
        End If

        '// Excel is now running
        If (ExcelApp is Nothing) Then
            eErrSts = ERR.EXCEL_START
            Exit Try        
        End If
        WCount = WCount + 1

		Dim oldCI As System.Globalization.CultureInfo = _
			System.Threading.Thread.CurrentThread.CurrentCulture
		System.Threading.Thread.CurrentThread.CurrentCulture = _
			New System.Globalization.CultureInfo("en-US")


        If (sPathName = "") Then
 			Debug.Print("Creating new ...")
           '// we're creating a new one
            ExcelWorkbook = ExcelApp.Workbooks.Add()
        Else
 			Debug.Print("Opening...")
            '// we're opening one
            If (System.IO.File.Exists(sPathName) = False) Then
				Debug.Print(sPathName & " not a file")
                eErrSts = ERR.NOTAFILE
                Exit Try
            End If
            ExcelWorkbook = ExcelApp.Workbooks.Open(sPathName)
            If (ExcelWorkbook Is Nothing) Then
				Debug.Print(sPathName & " exists but could not be opened")
                eErrSts = ERR.NOTAFILE
                Exit Try
            End If


        End If


    Catch ex As System.Exception
        eErrSts = ERR.EXCEPTION
        DebugException(ex)
    End Try
    #End If
    End Sub
    '// ===================================================================== '

	
    #If EMPTY_STUB=False
    '// --------------------------------------------------------------------- '
	Private Sub BeforeBookClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean)
		'This is called when you choose to close the workbook in Excel.
		'The event handlers are removed, and then the workbook is closed 
		'without saving changes.
		If (Wb Is ExcelWorkbook) Then
			ExcelApp.Visible = False
			ExcelApp.UserControl = False		
			Debug.WriteLine("Delegate: Closing the workbook and removing event handlers.")
			RemoveHandler ExcelApp.WorkbookBeforeClose, EventDel_BeforeBookClose
			eErrSts = ERR.OK
			Cancel = True
		End if
	End Sub	
    '// ===================================================================== '
    #End If
	
	
	
	
	
    '// --------------------------------------------------------------------- '
	Public Function GetSelByUser( _
		ByRef r as Integer, _
		ByRef c as Integer, _
		ByRef rCnt as Integer, _
		ByRef cCnt as Integer, _
		ByRef sASheet As String ) As Boolean
    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return False  
    #Else
	Dim bRet as Boolean = False
	Try
		
		'Add an event handler for the WorkbookBeforeClose Event of the
		'Application object.
		EventDel_BeforeBookClose = New Excel.AppEvents_WorkbookBeforeCloseEventHandler( _
			AddressOf BeforeBookClose)
		AddHandler ExcelApp.WorkbookBeforeClose, EventDel_BeforeBookClose
		'Make Excel visible and give the user control.
        eErrSts = ERR.BADINIT
		ExcelApp.Visible = True
		ExcelApp.UserControl = True		
		
		while (eErrSts = ERR.BADINIT)
			'// wait for the user to choose
			'// get the status of the excel
			if (ExcelApp.Workbooks.Count = 0) Then
				bRet = False
				Exit Try
			End if
		end while
        Dim shtcst As Excel.Worksheet
        shtcst = DirectCast(ExcelWorkbook.ActiveSheet, Excel.Worksheet)
        sASheet = shtcst.Name
        Dim oSel As Object = ExcelApp.ActiveWindow.Selection()
        If (TypeOf oSel Is Excel.Range) Then
            Dim rng As Excel.Range = DirectCast(oSel, Excel.Range)
            r = rng.Row
            c = rng.Column
            rCnt = rng.Rows.Count
            cCnt = rng.Columns.Count
		    bRet = True
        Else
		    bRet = False
        End If

    Catch ex As System.Exception
        eErrSts = ERR.EXCEPTION
        DebugException(ex)
    End Try
	Return bRet
    #End If
	End Function
    '// ===================================================================== '
	
    '// --------------------------------------------------------------------- '
    '// an instance of this class needs to be created for each workbook
    '// may either create a new worksheet or open an existing one
    Public Sub Close(ByVal Optional bSave As Boolean = False)
        
    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return      
    #Else
    Try
        If (ExcelWorkbook Is Nothing) Then
            eErrSts = ERR.BADINIT
            Exit Try
        End If
        ExcelWorkbook.Close(bSave)
        ReleaseComObject(ExcelWorkbook)
        ExcelWorkbook = Nothing
        WCount = WCount - 1
        If WCount < 0 Then
            WCount = 0
        End If

        '// see if we have Excel started;
        If Not (ExcelApp Is Nothing) Then
        If (ExcelApp.Workbooks.Count = 0) Then
            ExcelApp.Application.Quit()
            ReleaseComObject(ExcelApp)
            ExcelApp = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End If
        End If

    Catch ex As System.Exception
        eErrSts = ERR.EXCEPTION
        DebugException(ex)
    End Try
    #End If
    End Sub
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// get a sheet
    Public Function GetSheet( _
        Byval Optional index As Integer = Integer.MaxValue) _
        As XLS

    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return Nothing     
    #Else
        If ExcelWorkbook is Nothing Then return Nothing
        Dim sh As New XLS(Me,index)
        If (sh.ErrSts <> XLS.ERR.OK) Then
            Return Nothing
        End If

        Return sh
    #End If
    End Function
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// get a sheet
    Public Function GetSheet( _
        Byval name As String) _
        As XLS

    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return Nothing     
    #Else
        If ExcelWorkbook is Nothing Then return Nothing
        For Each sht as Object In ExcelWorkbook.Sheets
            Dim shtcst As Excel.Worksheet = DirectCast(sht, Excel.Worksheet)
            If name.ToUpper = shtcst.Name.ToUpper Then
                Dim sh As New XLS(Me,shtcst.Index)
                If (sh.ErrSts <> XLS.ERR.OK) Then
                    Return Nothing
                End If

                Return sh
            End If
        Next
        Return Nothing
    #End If
    End Function
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// get sheet list
    Public Function GetSheetList(ByRef sAry() As String, _
        ByRef iActive As Integer) As Boolean

    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return False     
    #Else
        If ExcelWorkbook is Nothing Then return False
        If ExcelWorkbook.Sheets.Count = 0 then Return False
        Dim shtcst As Excel.Worksheet
        Try

            ReDim sAry(0 to ExcelWorkbook.Sheets.Count-1)
            
            For Each sht as Object  In ExcelWorkbook.Sheets
                shtcst = DirectCast(sht, Excel.Worksheet)
                sAry(shtcst.Index-1) = shtcst.Name
            Next
            shtcst = DirectCast(ExcelWorkbook.ActiveSheet, Excel.Worksheet)
            iActive = shtcst.Index
        Catch ex As Exception
            Return False
        End Try
        Return True
    #End If
    End Function
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// get active sheet
    Public Function GetActiveSheet() _
        As XLS

    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return Nothing     
    #Else
        Dim sh As New XLS(Me,Integer.MaxValue)
        If (sh.ErrSts <> XLS.ERR.OK) Then
            Return Nothing
        End If

        Return sh
    #End If
    End Function
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// Just save
    Public Sub Save()
    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return     
    #Else
    Try
        If (ExcelWorkbook Is Nothing) Then
            eErrSts = ERR.BADINIT
            Exit Try
        End If
        ExcelWorkbook.Save()
    Catch ex As System.Exception
        eErrSts = ERR.EXCEPTION
        DebugException(ex)
    End Try
    #End If
    End Sub
    '// ===================================================================== '

    '// --------------------------------------------------------------------- '
    '// Just save
    Public Sub SaveAs(ByRef sFile As String)
    #If EMPTY_STUB
        eErrSts = ERR.BADINIT
        Return     
    #Else
    Try
        If (ExcelWorkbook Is Nothing) Then
            eErrSts = ERR.BADINIT
            Exit Try
        End If
        ExcelWorkbook.SaveAs(sFile, 56)
    Catch ex As System.Exception
        eErrSts = ERR.EXCEPTION
        DebugException(ex)
    End Try
    #End If
    End Sub
    '// ===================================================================== '


    '---------------------------------------------------------------------------------------------'
    Public Shared Sub ReleaseComObject(ByVal o As Object)
    #If EMPTY_STUB
        Return     
    #Else
        ' Credit: Dig-Boy
        'A safe way to explicitly release the resources associated with a COM object that has been called by the application.
        ' Without this call the resource will persist on the user's system as a running process that is unaccessible until it
        ' is killed in Task Manager or the computer is shut down.  Note that this is a two step process of first releasing 
        ' then setting the object to nothing.  DO NOT SET THE OBJECT TO NOTHING BEFORE RUNNING THIS METHOD.  Also, always 
        ' close down the object through its proper library methods before running this function (i.e. close documents/spreadsheets
        ' and Quit the Word/Excel app then run each of these objects through this function.
        Try
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o)
        Catch
            'Usually because the object is Nothing or has already been released...  Ignore.
        Finally
            If o IsNot Nothing Then o = Nothing
        End Try
    #End If
    End Sub
    '============================================================================================='

    '// --------------------------------------------------------------------- '
    '// Unified function for displaying exceptions
	Public Shared Sub DebugException(ByRef ex As System.Exception)

		Dim trace As System.Diagnostics.StackTrace = _
            New System.Diagnostics.StackTrace(ex, True)
		Dim exceptionLineNumber As Integer =  _
            trace.GetFrame(0).GetFileLineNumber()

		Debug.Print("Exception in " & trace.GetFrame(0).GetMethod.Name & vbCrLf & _
		 CStr(ex.Message) & "; " & vbCrLf & _
		 trace.GetFrame(0).ToString & vbCrLf)

	End Sub
    '// ===================================================================== '


End Class
'//  CLASS    =============================================================== '
'//
'//
'//
'//
'//  FUNCTIONS    ----------------------------------------------------------- '

'//  FUNCTIONS    =========================================================== '
'//
'//
'//
'//
'// ------------------------------------------------------------------------- '
'// ========================================================================= '
